{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ffabbb20",
   "metadata": {},
   "source": [
    "# 0.使用mysql登入数据库注意事项\n",
    "\n",
    "mysql连接数据库有二个关键点\n",
    "\n",
    "1. ip与端口\n",
    "\n",
    "   - ip与端口不同，再同一电脑上访问的可访问的数据库都会不同\n",
    "\n",
    "2. mysql用户名\n",
    "\n",
    "   - mysql账号不同，既然登录同一个ip与端口，访问数据库时的权限也会不同。\n",
    "\n",
    "     \n",
    "\n",
    "# 1.sql关键词\n",
    "\n",
    "## 1.0 show 关键词 （显示库/表的信息）\n",
    "\n",
    "show关键词的作用\n",
    "\n",
    "```mysql\n",
    "1.SHOW database; //显示该mysql账户下所有数据库名称\n",
    "2.SHOW [yourdatabase].[yourtable]; //显示yourdatabase下所有表名称\n",
    "3.SHOW columns from [yourdatabase].[yourtable]; \n",
    "4.desc [yourdatabase].[yourtable];  //3 4 等价用来描绘表的列名称，数据类型等信息\n",
    "5. show [full] processlist; //显示运行线程\n",
    "6.show grants; //查看当前用户权限\n",
    "7.show index from [yourdatabase].[yourtable]; //查看表的列索引，没有就返回空\n",
    "8.show table status from [yourdatabase]; //显示目标数据库下每个表的创建时间，最后一次更新时间 没有from的话需要use进入某个数据库\n",
    "9. show errors/warnings; //显示最后一次错误\n",
    "```\n",
    "\n",
    "注意点\n",
    "\n",
    "1.  语句2 3，未使用use指定数据库。再使用sql操作表时需要声明操作的是那个数据库的那张表\n",
    "2. 语句5 显示用户正在运行的线程\n",
    "   1. 只有root用户可以看见所以运行的线程，其他用户只能看见自己的。\n",
    "   2. 信息来于 `select * from information_schema.processlist`\n",
    "   3. 意义：如何使用processlist进行性能优化。\n",
    "   4. 如果发生数据库阻塞，就可以通过kill干掉不相关的线程；\n",
    "3. 语句7 注意没有index列索引的table是存在的。\n",
    "\n",
    "\n",
    "\n",
    "## 1.1 use 关键词 （切换库）\n",
    "\n",
    "```mysql\n",
    "1. use A //进入A数据库，之后使用A下面的表时不需要强调该表属于哪个数据库。\n",
    "2. use A;USE B; //从A数据库切换到B数据库\n",
    "3.select * from [yourdatabase].[yourtable]; //使用USE后，采用强调表的归属数据库的查询语句不影响\n",
    "4.select database(); //查看当前使用的数据库\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "## 1.2 source 关键词 （执行sql文件）\n",
    "\n",
    "```mysql\n",
    "source mysqldemo.sql //进入mysql前先进入sql文件的目录\n",
    "source D:\\\\XX\\\\xx\\\\XX\\\\mysqldemo.sql //双斜杠防止转义\n",
    "\n",
    "/*source就是顺序执行文件中的SQL，如果中间出现了错误中断就停止了，而且不会回滚。source命令执行导入的sql脚本属于追加方式，严格按照脚本内容执行。*/\n",
    "```\n",
    "\n",
    "![image-20230821085821988](C:\\Users\\10022\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230821085821988.png)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "## 1.3 SELECT 关键词 （列操作）\n",
    "\n",
    "**注意：**\n",
    "\n",
    "- **1.不管那种select语句，都必须含有from子句，**\n",
    "- **2.选列一般人为选择，因为列名是自定义的**\n",
    "\n",
    "完整的select语句为\n",
    "\n",
    "```mysql\n",
    "//from子句是必须的，而且运行顺序也是先from，再select；先选表，再选列，最后选行\n",
    "SELECT\n",
    "    column_1, column_2, ...\n",
    "FROM\n",
    "    table_1\n",
    "[INNER | LEFT |RIGHT] JOIN table_2 ON conditions\n",
    "WHERE\n",
    "    conditions\n",
    "GROUP BY column_1\n",
    "HAVING group_conditions\n",
    "ORDER BY column_1\n",
    "LIMIT  offset,length; \n",
    "\n",
    " \n",
    "```\n",
    "\n",
    "参数解释：\n",
    "\n",
    "- SELECT 后跟逗号分隔列或星号（*）列表，表示返回所有列。 \n",
    "- FROM 指定要查询数据的表或视图。\n",
    "- [JOIN](https://www.begtut.com/mysql/mysql-join.html) 根据特定的连接条件从其他表中获取相关数据。\n",
    "- [WHERE](https://www.begtut.com/mysql/mysql-where.html) 子句过滤结果集中的行。\n",
    "- [GROUP BY](https://www.begtut.com/mysql/mysql-group-by.html) 子句将一组行分组到组中，并在每个组上应用[聚合函数](https://www.begtut.com/mysql/mysql-aggregate-functions.html)。\n",
    "- [HAVING ](https://www.begtut.com/mysql/mysql-having.html) 子句根据GROUP BY子句定义的组过滤组。\n",
    "- [ORDER BY](https://www.begtut.com/mysql/mysql-order-by.html) 子句指定用于排序的列的列表。\n",
    "- [LIMIT](https://www.begtut.com/mysql/mysql-limit.html) 约束返回的行数。\n",
    "\n",
    "注意：\n",
    "\n",
    "1. 使用select时**不要随意使用***，①浪费性能②影响观感③暴露敏感数据\n",
    "2. **除了from外，其他每个参数都不是必须的。**\n",
    "\n",
    "\n",
    "\n",
    "#### [特殊子句] *FROM关键词（选表/表筛选）\n",
    "\n",
    "①选择的表只有一张\n",
    "\n",
    "```mysql\n",
    "select * from XXX  //全列\n",
    "select A,B from XX //筛选目标列 \n",
    "```\n",
    "\n",
    "②选择的表有n张 \n",
    "\n",
    "```mysql\n",
    "//0.强行将两张表联结，不给联结条件 ~ 返回笛卡尔积\n",
    "select A.student_id,B.officer_id \n",
    "FROM student,officer;\n",
    "//1.隐式内联结(与2.返回结果一致，表述方式不同)\n",
    "select A.student_id,B.officer_id \n",
    "FROM student A,officer B\n",
    "WHERE A.student_id = B.officer_id;\n",
    "\n",
    "//2.内连接 ~ 将两张表按联结条件进行合并返回\n",
    "select A.student_id,..., \n",
    "FROM student AS A\n",
    "INNER JOIN officers AS B\n",
    "ON  A.student_id = B.officer_id ;\n",
    "\n",
    "//外连接~2.基础上会偏左/偏右/双偏返回\n",
    "select A.student_id,B.officer_id \n",
    "FROM student AS A\n",
    "LEFT/RIGHT/FULL JOIN officers AS B \n",
    "ON A.student_id = B.officer_id ;\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "#### 1.3.1 AS 关键词（给列/字段取别名）\n",
    "\n",
    "AS的意义是\n",
    "\n",
    "1. 简化语法，比如我们使用计算字段时\n",
    "2. 涉及过多表时，用别名防止出现歧义\n",
    "\n",
    "AS的两个用处\n",
    "\n",
    "1. 给表取别名 \n",
    "\n",
    "   意义：\n",
    "\n",
    "   ```mysql\n",
    "   select * from blog as B;\n",
    "   //此时 blog表的别名就叫B了\n",
    "   select * from blog B;\n",
    "   //注意 AS 是可以省略不写的\n",
    "   ```\n",
    "\n",
    "2. 给字段/列取别名\n",
    "\n",
    "   意义： \n",
    "\n",
    "   ```mysql\n",
    "   select  column_1  as c1,column_2 as c2   from  text as  t1;\n",
    "   //此时列名分别叫 c1，c2,表名叫t1\n",
    "   \n",
    "   ```\n",
    "\n",
    "3. as可以作为连接语句的操作符。（要了解）\n",
    "\n",
    "   ```mysql\n",
    "   create table  tablename as select * from tablename2;\n",
    "   //先获取到tablename表中的所有记录，之后创建一张tablename表，结构和tablename2表相同，记录为后面语句的查询结果。\n",
    "   ```\n",
    "\n",
    "   \n",
    "\n",
    "#### 1.3.2  自定义字段 （函数字段+计算字段/表达式字段）    \n",
    "\n",
    "计算字段有两种：str的拼接字段，与float的计算字段/表达式\n",
    "\n",
    "- 拼接字段  （函数字段）\n",
    "\n",
    "```mysql\n",
    "//假如我们姓名字段是分开记录，若我们想输出姓名列就需要拼接\n",
    "select Concat(xing,\"_\",ming) from XXX\n",
    "```\n",
    "\n",
    "注意：因为该字段是计算字段，若我们为给他取别名，则该列名就会是函数名\n",
    "\n",
    "```mysql\n",
    "select Concat(xing,\"_\",ming) as name from XXX as name_table\n",
    "//之后输出的列名就叫name列了\n",
    "```\n",
    "\n",
    "- 计算字段 （表达式字段）\n",
    "\n",
    "```mysql\n",
    "//假如要求某商品的销售额  （销售额=单价×销售量）\n",
    "select prod_id,\n",
    "       price,\n",
    "       num,\n",
    "       price*num AS income\n",
    "from orderitems;\n",
    "// 若想要其他结果，仅需要四则运算组合就可得到\n",
    "```\n",
    "\n",
    "- 聚合字段 （函数字段）\n",
    "\n",
    "```mysql\n",
    "// 聚合函数的出现分两种；1.单独出现 2.与group by分组出现\n",
    "详情后几章，此处略\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "## 1.4 select语句的子句（行操作）\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "### 1.4.1 distinct关键词（去重复行）\n",
    "\n",
    "distinct关键字的作用是对表中的单个字段或多个字段去重操作。\n",
    "\n",
    "1. 单个字段去重\n",
    "\n",
    "   ```mysql\n",
    "   select distinct A_col from A_table;\n",
    "   //结果： A_col 只会出现不同的值\n",
    "   ```\n",
    "\n",
    "2. 多个字段去重\n",
    "\n",
    "   ```mysql\n",
    "   select distinct  A_col,B_col  from A_table;\n",
    "   //结果： 先将 A_col,B_col  看作一个整体，所以 A_val,B_val 与 B_val,A_val 是不同的\n",
    "   //与order by 多列排序一致\n",
    "   ```\n",
    "\n",
    "3. 给聚合函数的去重（将要聚合的值去重，在聚合） [后面会遇到]\n",
    "\n",
    "   ```mysql\n",
    "   select avg(distinct prod_price) as avg_price \n",
    "   from prodects;\n",
    "   //将prod_price中重复值去掉后，再求均值。其他聚合函数同理 \n",
    "   ```\n",
    "\n",
    "   \n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "### 1.4.2 WHERE关键词（筛选目标行）\n",
    "\n",
    "\n",
    "\n",
    "#### 1.4.2.0 where与select的组合出现情况\n",
    "\n",
    "一条查询语句中可能出现\n",
    "\n",
    "- 一个select，一个where  （where中只含单个运算符） 单条件查询\n",
    "\n",
    "- 一个select，一个where，但条件是多个的 （and，or，not连接条件） 多条件查询 \n",
    "\n",
    "- 多个select，多个where （union/复合查询）/ （子查询）   组合多条件查询\n",
    "\n",
    "  \n",
    "\n",
    "#### 1.4.2.1  一个select，一个where查询\n",
    "\n",
    "##### ①筛选已知值/未知值 时用的运算符\n",
    "\n",
    "- 已知值：知道它是某一具体的值，或者它属于某一个区间   \n",
    "\n",
    "\n",
    "```mysql\n",
    "//例1：具体值\n",
    "SELECT * FROM Customers\n",
    "WHERE CustomerID=1;\n",
    "//例2：属于某个区间\n",
    "SELECT * FROM Customers\n",
    "WHERE CustomerID<1;\n",
    "```\n",
    "\n",
    "- 常用筛选已知值的运算符有\n",
    "\n",
    "  - `=,>,<,...,!=`\n",
    "\n",
    "  - `BETWEEN AND,IN`\n",
    "\n",
    "- 未知值：常用筛选未知值的运算符有两个 \n",
    "- 常用筛选未知值的运算符有：\n",
    "  - `LIKE 与 REGEXP`\n",
    "\n",
    "\n",
    "\n",
    "##### ②筛选字符串，数值，日期，时间\n",
    "\n",
    "1. 筛选字符串\n",
    "   1. 过滤已知值：`=,>,<,!=,...`\n",
    "   3. 过滤已知值/模糊匹配：`LIKE与REGEXP`\n",
    "2. 筛选时间，日期，数值\n",
    "   1. 精确匹配： `BETWEEN AND，=,>,<,...`\n",
    "   3. 模糊匹配：`REGEXP,LIKN`\n",
    "\n",
    "- 实例1：筛选数值\n",
    "\n",
    "  ```mysql\n",
    "  // 找buyprice 在 90 ~ 100 之间的数值 \n",
    "  1.SELECT  \n",
    "      productCode, \n",
    "      productName, \n",
    "      buyPrice\n",
    "  FROM\n",
    "      products\n",
    "  WHERE\n",
    "      buyPrice BETWEEN 90 AND 100;  \n",
    "  // 1式等价表达\n",
    "  2.SELECT \n",
    "      productCode, \n",
    "      productName, \n",
    "      buyPrice\n",
    "  FROM\n",
    "      products\n",
    "  WHERE\n",
    "      buyPrice >= 90 AND buyPrice <= 100; \n",
    "  //1.2式对偶表达\n",
    "  3.SELECT \n",
    "      productCode, \n",
    "      productName, \n",
    "      buyPrice\n",
    "  FROM\n",
    "      products\n",
    "  WHERE\n",
    "      buyPrice NOT BETWEEN 90 AND 100; \n",
    "  ```\n",
    "\n",
    "- 实例2：筛选日期\n",
    "\n",
    "  **因为所需日期列的数据类型是`DATE`的，所以我们使用`CAST`运算符将文字字符串'2013-01-01'和'2013-12-31'转换为`DATE`值。** （好习惯，不管是不是都转一下）\n",
    "\n",
    "  ```mysql\n",
    "  // 回在requireddate列中处于2003/01/01到2003/01/31日期之间订单：\n",
    "  1.SELECT \n",
    "     orderNumber,\n",
    "     requiredDate,\n",
    "     status\n",
    "  FROM \n",
    "     orders\n",
    "  WHERE \n",
    "     requireddate BETWEEN \n",
    "       CAST('2013-01-01' AS DATE) AND \n",
    "       CAST('2013-01-31' AS DATE); \n",
    "  \n",
    "  ```\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "#### 1.4.2.0 一个select，一个where，但条件是多个的\n",
    "\n",
    "##### ①A and B（同时满足两个条件）\n",
    "\n",
    "```mysql\n",
    "SELECT    \n",
    " customername, \n",
    " country, \n",
    " state, \n",
    " creditlimit\n",
    "FROM    \n",
    " customers\n",
    "WHERE country = 'USA'\n",
    " AND state = 'CA'\n",
    " AND creditlimit > 100000; \n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "##### ②A or B（至少满足一个条件）\n",
    "\n",
    "```mysql\n",
    "SELECT    \n",
    " customername, \n",
    " country\n",
    "FROM    \n",
    " customers\n",
    "WHERE country = 'USA'\n",
    " OR country = 'France'; \n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "##### ③ NOT 否定where条件（求where的对偶条件）\n",
    "\n",
    "##### ④以上三种可以任意排列组合，执行顺序与位置有关，与是否有括号也有关；\n",
    "\n",
    "\n",
    "\n",
    "#### 1.4.2.1 多条件符合查询（略）\n",
    "\n",
    "\n",
    "\n",
    "### 1.4.3 order by 关键词（列排序）\n",
    "\n",
    "ORDER BY关键字默认按升序对记录进行排序。要按降序对记录进行排序，请使用DESC关键字。\n",
    "\n",
    "```mysql\n",
    "// 单列排序\n",
    "SELECT * FROM Customers\n",
    "ORDER BY Country DESC;\n",
    "// 多列排序\n",
    "SELECT * FROM Customers\n",
    "ORDER BY Country, CustomerName; \n",
    "```\n",
    "\n",
    "注意点\n",
    "\n",
    "1. DESC要写再 order by后面，需要降序关键词前面。\n",
    "\n",
    "2. 多列排序，先排Country列，当Country列相同，才会去排CustomerName列。\n",
    "\n",
    "   \n",
    "\n",
    "\n",
    "\n",
    "### 1.4.4 group by关键词 （分组，同时发生聚合）\n",
    "\n",
    "使用group by须知：\n",
    "\n",
    "1. 含group by的语句结构\n",
    "\n",
    "   ```mysql\n",
    "   SELECT column1, column2, ..., aggregate_function(column_name)\n",
    "   FROM table_name\n",
    "   WHERE conditions\n",
    "   GROUP BY column1, column2, ...;\n",
    "   //aggregate_function是要对某一列计算的聚合函数，\n",
    "   //column_name是要作为GROUP BY条件的列(要聚合的列）\n",
    "   //以column1, column2为组名\n",
    "   ```\n",
    "\n",
    "   \n",
    "\n",
    "2. GROUP BY列名相同的行到汇到一组中，所以group必须与聚会函数混用，不然显示的结果无意义。\n",
    "\n",
    "3. GROUP BY语句通常与聚合函数（COUNT，MAX，MIN，SUM，AVG）一起使用，将结果集分组为一列或多列。\n",
    "\n",
    "4. 以多个列分组时，不同顺序会使得组名不同\n",
    "\n",
    "   ```mysql\n",
    "   col1 col2 count\n",
    "   A     B     1\n",
    "   A     A     1\n",
    "   B     B     1\n",
    "   B     A     1\n",
    "   A     B     2 /*其中只有1,5算同一组 分组,并对count组进行求和聚合就会出现；A B 与B A 不是组名不同*/\n",
    "   col1 col2 sum(count)\n",
    "   A     B     3\n",
    "   A     A     1\n",
    "   B     B     1\n",
    "   B     A     1\n",
    "   ```\n",
    "\n",
    "5. group by后面跟的列，可以是计算字段/表达式，但不能是聚合函数\n",
    "\n",
    "6. 在select时给计算字段取别名时，group by只能跟计算字段的表达式不能用别名；\n",
    "\n",
    "①单列分组，聚合\n",
    "\n",
    "```mysql\n",
    "SELECT customer_id, SUM(order_amount) AS total_amount\n",
    "FROM order\n",
    "GROUP BY customer_id;\n",
    "/* 以customer_id列为组名进行分组，\n",
    "然后将同一组的order_amount进行加总得到求和值total_amount。*/\n",
    "```\n",
    "\n",
    "②多列分组，聚合\n",
    "\n",
    "```mysql\n",
    "SELECT department_id, salary, AVG(salary) AS avg_salary\n",
    "FROM employee\n",
    "GROUP BY department_id, salary;\n",
    "/* 以department_id, salary列为组名进行分组，然后将同一组的salary进行加总然后除以组数，得到的平均值avg_salary。*/\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "#### 1.4.4.1 having 关键词（筛选组）\n",
    "\n",
    "③先分组，聚合。后进行条件过滤（必须用HAVING筛选）\n",
    "\n",
    "```mysql\n",
    "SELECT customer_id, SUM(order_amount) AS total_amount\n",
    "FROM order\n",
    "GROUP BY customer_id\n",
    "HAVING total_amount > 1000;\n",
    "/*以customer_id为组名，将同一组的order_amount求和得到total_amount，然后再筛选total_amount>1000的数据展示出来\n",
    "*/\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "- where与having的区别\n",
    "\n",
    "  1. **WHERE 不能使用聚合函数作为筛选条件；**（having可以，见上例）\n",
    "\n",
    "  2. **HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条**。\n",
    "\n",
    "  3. **WHERE 是先筛选后连接，而 HAVING 是先连接后筛选**。（若涉及到**需要通过连接从关联表中获取需要的数据**）\n",
    "\n",
    "  4. where与having可以同时使用；\n",
    "\n",
    "     \n",
    "\n",
    "#### 1.4.4.2 rollup 关键词（分组的同时，生成组小结）\n",
    "\n",
    "①rollup 关键词解释\n",
    "\n",
    "```mysql\n",
    "//注意 rollup是group by的可选子句，不要单独分行了\n",
    "//一般rollup前面要跟with\n",
    "\n",
    "SELECT \n",
    "    productLine, \n",
    "    SUM(orderValue) totalOrderValue\n",
    "FROM\n",
    "    sales\n",
    "GROUP BY \n",
    "    productline WITH ROLLUP;\n",
    "//以productline为组名后，求各组orderValue的总计的同时；\n",
    "//再填加with rollup,还会返回一行将每个组的orderValue再进行求和一次；\n",
    "+------------------+-----------------+\n",
    "| productLine      | totalOrderValue |\n",
    "+------------------+-----------------+\n",
    "| Classic Cars     |        19668.13 |\n",
    "| Motorcycles      |         9044.15 |\n",
    "| Planes           |        11700.79 |\n",
    "| Ships            |        13147.86 |\n",
    "| Trains           |         9021.03 |\n",
    "| Trucks and Buses |        14194.95 |\n",
    "| Vintage Cars     |        12245.78 |\n",
    "| NULL             |        89022.69 |\n",
    "+------------------+-----------------+\n",
    "//NULL行就是上几行的求和;\n",
    "\n",
    "//注意如果是多行分组（可能会出现多个null，共计值）\n",
    "+------------------+-----------+-----------------+\n",
    "| productLine      | orderYear | totalOrderValue |\n",
    "+------------------+-----------+-----------------+\n",
    "| Classic Cars     |      2013 |         5571.80 |\n",
    "| Classic Cars     |      2014 |         8124.98 |\n",
    "| Classic Cars     |      2015 |         5971.35 |\n",
    "| Classic Cars     |      NULL |        19668.13 |\n",
    "| Motorcycles      |      2013 |         2440.50 |\n",
    "| Motorcycles      |      2014 |         2598.77 |\n",
    "| Motorcycles      |      2015 |         4004.88 |\n",
    "| Motorcycles      |      NULL |         9044.15 |\n",
    "```\n",
    "\n",
    "②GROUPING() 函数\n",
    "\n",
    "会在\n",
    "\n",
    "```mysql\n",
    "SELECT \n",
    "    orderYear,\n",
    "    productLine, \n",
    "    SUM(orderValue) totalOrderValue,\n",
    "    GROUPING(orderYear),\n",
    "    GROUPING(productLine)\n",
    "FROM\n",
    "    sales\n",
    "GROUP BY \n",
    "    orderYear,\n",
    "    productline\n",
    "WITH ROLLUP; \n",
    "\n",
    "//result\n",
    "+-----------+------------------+-----------------+---------------------+-----------------------+\n",
    "| orderYear | productLine      | totalOrderValue | GROUPING(orderYear) | GROUPING(productLine) |\n",
    "+-----------+------------------+-----------------+---------------------+-----------------------+\n",
    "|      2013 | Classic Cars     |         5571.80 |                   0 |                     0 |\n",
    "|      2013 | Motorcycles      |         2440.50 |                   0 |                     0 |\n",
    "|      2013 | Planes           |         4825.44 |                   0 |                     0 |\n",
    "|      2013 | Ships            |         5072.71 |                   0 |                     0 |\n",
    "|      2013 | Trains           |         2770.95 |                   0 |                     0 |\n",
    "|      2013 | Trucks and Buses |         3284.28 |                   0 |                     0 |\n",
    "|      2013 | Vintage Cars     |         4080.00 |                   0 |                     0 |\n",
    "|      2013 | NULL             |        28045.68 |                   0 |                     1 |\n",
    "|      2014 | Classic Cars     |         8124.98 |                   0 |                     0 |\n",
    "|      2014 | Motorcycles      |         2598.77 |                   0 |                     0 |\n",
    "|      2014 | Planes           |         2857.35 |                   0 |                     0 |\n",
    "|      2014 | Ships            |         4301.15 |                   0 |                     0 |\n",
    "|      2014 | Trains           |         4646.88 |                   0 |                     0 |\n",
    "|      2014 | Trucks and Buses |         4615.64 |                   0 |                     0 |\n",
    "|      2014 | Vintage Cars     |         2819.28 |                   0 |                     0 |\n",
    "|      2014 | NULL             |        29964.05 |                   0 |                     1 |\n",
    "|      2015 | Classic Cars     |         5971.35 |                   0 |                     0 |\n",
    "|      2015 | Motorcycles      |         4004.88 |                   0 |                     0 |\n",
    "|      2015 | Planes           |         4018.00 |                   0 |                     0 |\n",
    "|      2015 | Ships            |         3774.00 |                   0 |                     0 |\n",
    "|      2015 | Trains           |         1603.20 |                   0 |                     0 |\n",
    "|      2015 | Trucks and Buses |         6295.03 |                   0 |                     0 |\n",
    "|      2015 | Vintage Cars     |         5346.50 |                   0 |                     0 |\n",
    "|      2015 | NULL             |        31012.96 |                   0 |                     1 |\n",
    "|      NULL | NULL             |        89022.69 |                   1 |                     1 |\n",
    "+-----------+------------------+-----------------+---------------------+-----------------------+\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "③rollup关键词与grouping函数的实际意义\n",
    "\n",
    "```mysql\n",
    "SELECT \n",
    "    IF(GROUPING(orderYear),\n",
    "        'All Years',\n",
    "        orderYear) orderYear,\n",
    "    IF(GROUPING(productLine),\n",
    "        'All Product Lines',\n",
    "        productLine) productLine,\n",
    "    SUM(orderValue) totalOrderValue\n",
    "FROM\n",
    "    sales\n",
    "GROUP BY \n",
    "    orderYear , \n",
    "    productline \n",
    "WITH ROLLUP; \n",
    "//把null值，替换成了其他红色字符串\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "### 1.4.6 limit关键词（结果部分展示/输出行数限制）\n",
    "\n",
    "limit的作用\n",
    "\n",
    "1. 进过筛选后的返回行数过多，可以只展示部分\n",
    "\n",
    "   ```mysql\n",
    "   select A_col from A_table  limit 2 ; // 代表只展示前两行\n",
    "   \n",
    "   select A_col from A_table  limit 5,5 ; // 代表从第五行开始展示，向下展示5行。\n",
    "   \n",
    "   ```\n",
    "\n",
    "2. limit 1 与order by组合，求出某列的最值\n",
    "\n",
    "   ```mysql\n",
    "   // 等价于聚合函数的max 与 min\n",
    "   select A_col \n",
    "   from A_table \n",
    "   order by A_col   \n",
    "   limit 1;\n",
    "   \n",
    "   //求最小值\n",
    "   select A_col \n",
    "   from A_table \n",
    "   order by  A_col desc  \n",
    "   limit 1;\n",
    "   // ASC 最小  DESC 最大\n",
    "   ```\n",
    "\n",
    "   注意：这里的desc关键词 与描述表信息的 desc table_name 意义不同。\n",
    "\n",
    "   desc 是【 describe v.描述】 和【descend v.下降】 的缩写。 \n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "​\t\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "## 1.5 select的复杂查询\n",
    "\n",
    "\n",
    "\n",
    "### 1.5.0 view关键字 （视图） / sql语句封装\n",
    "\n",
    "> 视图：虚拟表，一种存储sql语句的方法。\n",
    ">\n",
    "> 从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用`SELECT语句`查询数据，也可以使用`INSERT，UPDATE，DELETE`修改记录，视图可以使用户操作方便，并保障数据库系统安全。\n",
    "\n",
    "\n",
    "\n",
    "#### 1.5.0.0 视图的意义\n",
    "\n",
    "-  可以将复杂的多表查询封装成视图，在查询的时候只需要简单的一个查询就能得到想要的结果。\n",
    "- 注意在创建视图中包含聚合函数、DISTINCT 去重、GROUP BY 分组、HAVING 筛选、UNION ALL 查询结果合并的时候，视图不可以更新。\n",
    "\n",
    "\n",
    "\n",
    "#### 1.5.0.1 视图与表的区别\n",
    "\n",
    "\n",
    "\n",
    "| name | 存储内容 |               访问时发生了什么               |       缺点        |\n",
    "| :--: | :------: | :------------------------------------------: | :---------------: |\n",
    "|  表  | 实际数据 |       读取实际存储的数据，然后返回结果       |       麻烦        |\n",
    "| 视图 | sql语句  | ①运行存储的sql语句②得到一张临时表③返回临时表 | ①不能使用order by |\n",
    "\n",
    "注意：删除视图时，要使用`drop view`\n",
    "\n",
    "\n",
    "\n",
    "#### 1.5.0.2  创建视图\n",
    "\n",
    "```mysql\n",
    "create view 视图名(视图列名1,视图列名2,...,视图列名)\n",
    "as <select语句>;\n",
    "//<select语句>就是我们view中存储的语句\n",
    "//视图列名1需要与select语句返回的表一一对应，表结构与表数据不变，但是表的列名按照视图列名算\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "#### 1.5.0.3 使用视图\n",
    "\n",
    "```mysql\n",
    "select 视图名(视图列名1,视图列名2,...,视图列名)\n",
    "from 视图名;\n",
    "//与创建时保持一致即可\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "#### 1.5.0.4   视图的修改\n",
    "\n",
    "\n",
    "\n",
    "语法一的意思是**兼具视图创建和修改的功能**，视图如果存在就修改，如果视图不存在就创建视图。而语法二**只有修改功能**。\n",
    "\n",
    "**语法一：** CREATE OR REPLACE VIEW 视图名 AS 查询语句；\n",
    "\n",
    "**语法二：** ALTER VIEW 视图名 AS 查询语句；\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "### 1.5.1 子查询  \n",
    "\n",
    "> MySQL子查询：称为内部查询，而包含子查询的查询称为外部查询。子查询可以在任何使用表达式的地方使用，并且必须在括号中关闭。如 from子句，where子句...\n",
    "\n",
    "\n",
    "\n",
    "```mysql\n",
    "//子查询格式类似如下：\n",
    "SELECT name FROM tb_students_info \n",
    "WHERE course_id NOT IN (SELECT id FROM tb_course WHERE course_name = 'Java');\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "#### 1.5.1.1 子查询 （一次性视图）\n",
    "\n",
    "- 子查询适用范围：需要用表，或需要用视图的地方，就可以用子查询；\n",
    "\n",
    "- 注意的点：使用子查询时，最好再加一个 AS 视图名，即使子查询是临时表，查询结束就会消失；\n",
    "\n",
    "- 子查询的执行顺序：先执行最内层查询的，再执行外面一层，类推到最外面；\n",
    "\n",
    "  \n",
    "\n",
    "#### 1.5.1.2 标量子查询（只返回一行一列结果的子查询）\n",
    "\n",
    "- 不同之处：标量子查询返回的结果是1行1列；\n",
    "- 意义：\n",
    "  1. 子查询可以替代需要表的地方，标量子查询可以**替代需要常数的地方**\n",
    "  2. where需要用到聚合函数时，**改写where中的聚合函数（前提是只能1行1列）**【因为where不能用聚合函数】\n",
    "- 实际例子：求出高于商品平均价格的单排\n",
    "  1. select+聚合函数求出，商品均价AVG\n",
    "  2. select+where price>AVG；这些就可以选出高于均值的商品\n",
    "  3. 我们知道 where子句是不能直接使用聚合函数的；这时就可以用标量子查询代替\n",
    "\n",
    "```\n",
    "select col_A,col_B,col_C\n",
    "from table_d\n",
    "where sale_price > avg(sale_price); //注意，where不能直接用聚合\n",
    "//改一下\n",
    "select col_A,col_B,col_C\n",
    "from table_d\n",
    "where sale_price > (select avg(sale_price) from table_d) ; //注意，where不能直接用聚合\n",
    "\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "#### 1.5.1.3 关联子查询\n",
    "\n",
    "- 关联子查询查询顺序\n",
    "\n",
    "  1. 先执行外部查询，并且按顺序执行\n",
    "\n",
    "  2. 当按顺序执行到遇到内部查询时，将外部查询中的关联值传递给内部查询\n",
    "\n",
    "  3. 内部查询运行后得到关联结果返回给外部查询\n",
    "\n",
    "  4. 得到返回结果后，外部查询继续查询知道完成这一行；\n",
    "\n",
    "  5. 重复上述操作知道外部查询到最后一行；\n",
    "\n",
    "     1. 外部子句执行一行，内部子句执行一轮 若外部有n行，内部有m行；\n",
    "     2. 那么一个关联子查询会执行n×m次\n",
    "\n",
    "     \n",
    "\n",
    "- 使用关联子查询的情况：在组内进行比较时会使用【与分组聚合完全不同】\n",
    "\n",
    "- 关联子查询格式如下\n",
    "\n",
    "  ```\n",
    "  select col_A,col_B,col_C\n",
    "  from product as p1\n",
    "  where sale_price > (select avg(sale_price) \n",
    "  \t\t\t\t\tfrom product as p2\n",
    "  \t\t\t\t\twhere p1.type = p2.type\n",
    "  \t\t\t\t\tgroup by type);\n",
    "  //关键在于where p1.type = p2.type，将外部查询与内部查询连接了起来；\n",
    "  \n",
    "  ```\n",
    "\n",
    "  \n",
    "\n",
    "\n",
    "\n",
    "### 1.4.10 组合查询 （union 关键词）\n",
    "\n",
    "> 组合查询：将多个select语句的结合合并，然后以单个结果集的形式返回\n",
    ">\n",
    "> 组合查询/并查询/复合查询;\n",
    "\n",
    "①运用组合查询的地方\n",
    "\n",
    "1. 通过单个查询，从不同表中返回类似的数据结构\n",
    "2. 单个表多个查询，以单个表形式返回数据；\n",
    "\n",
    "②在筛选同一张表上的数据时，uion与where or与where in 之间可以互相转化（甚至要复杂一些）；\n",
    "\n",
    "​    在筛选不同的表上的数据时，uion比其他两种简单得多   \n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6bb002a4",
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-6-739a58173b0e>, line 30)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  File \u001b[0;32m\"<ipython-input-6-739a58173b0e>\"\u001b[0;36m, line \u001b[0;32m30\u001b[0m\n\u001b[0;31m    ```mysql\u001b[0m\n\u001b[0m    ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "# 2.SQL中的函数\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "## 2.1 文本处理函数 （字符串函数）\n",
    "\n",
    "```mysql\n",
    "concat()  //将多个字符串拼接\n",
    "\n",
    "left()    //返回字符串左边的字符\n",
    "right()   //返回字符串右边的字符\n",
    "\n",
    "length()  //返回字符串的长度\n",
    "locate()  //返回字符串的一个子串\n",
    "soundex() //返回soundex值\n",
    "substring()//返回子串的字符\n",
    "\n",
    "lower()   //字符串转小写\n",
    "upper()   //字符串转大写\n",
    "\n",
    "ltrim()   //去掉字符串右侧空格\n",
    "rtrim()   //去掉字符串左侧空格\n",
    "\n",
    "\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "## 2.2 日期时间处理函数 （日期函数）\n",
    "\n",
    "\n",
    "\n",
    "```mysql\n",
    "adddate()\t\t//增加一个日期（天、周等）\n",
    "addtime()\t\t//增加一个时间（时、分等）\n",
    "\n",
    "now()\t\t\t//返回当前日期和时间\n",
    "curdate()\t\t//返回当前日期  \n",
    "curtime()\t\t//返回当前时间\n",
    "\n",
    "date()\t\t\t//返回时间节点的日期部分   2023/01/01 00:00 变成 2023/01/01 \n",
    "datediff()\t\t//计算两个日之差\n",
    "date_add()\t\t//高度灵活的日期运算函数\n",
    "date_format()\t//返回一个格式化的日期或时间串\n",
    "dayofweek()\t\t//求一个日期对于的是星期几\n",
    "day()\t\t\t//返回日期的日部分   2023/01/01 00:00 变成 01\n",
    "month()\t\t\t//返回日期的月部分   2023/01/01 00:00 变成 01\n",
    "year()\t\t\t//返回日期的年部分   2023/01/01 00:00 变成 2023\n",
    "\n",
    "time()\t\t\t//返回时间节点的时间部分2023/01/01 00:00 变成 00:00\n",
    "hour()\t\t\t//小时部分\n",
    "minute()\t\t//分钟部分\n",
    "second()\t\t//秒钟部分\n",
    "\n",
    "```\n",
    "\n",
    "注意：\n",
    "\n",
    "1. mysql的日期格式均为\"YYYY-MM-DD\"\n",
    "\n",
    "2. 如果你需要的是日期，请使用一定要用date()这是一个好习惯；\n",
    "\n",
    "   ```mysql\n",
    "   //返回order_date在05年9.1~9.30的所有数据\n",
    "   select cust_id,order_num \n",
    "   from orders\n",
    "   where date(order_date) betweeen \"2005-09-01\" and '2005-09-30';\n",
    "   //因为不确定order_date的数据类型，所有不管是不是都要date一下\n",
    "   ```\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "## 2.3 数值处理函数 （算术函数）\n",
    "\n",
    "```mysql\n",
    "+,-,*,/   //四则运算  \n",
    "ABS() \t  //返回绝对值\n",
    "SQRT()    //求平方\n",
    "SIN()     //求sin值\n",
    "COS()     //cos值\n",
    "TAN()     //tan值\n",
    "EXP()     //e的n次法\n",
    "PI()      //π\n",
    "RAND()    //随机数返回\n",
    "\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "## 2.4 聚合函数   \n",
    "\n",
    "```mysql\n",
    "AVG()  //均值\n",
    "COUNT()//计数\n",
    "MAX()  //最大\n",
    "MIN()  //最小\n",
    "SUM()  //求和\n",
    "// trip1：添加关键字distinct，可以进行不同值聚合   AVG(DISTINCT prod_price) \n",
    "// trip2：聚合函数中的参数，可以是计算字段 ；但加了distinct后就不能跟计算字段了\n",
    "```\n",
    "\n",
    "注意\n",
    "\n",
    "1. 聚合函数单独出现，表示（返回指定列的信息）；但是分组不能脱离聚合函数，否则失去意义\n",
    "\n",
    "   ```mysql\n",
    "   //返回prod_price列的平均值\n",
    "   select avg(prod_price) as avg_price \n",
    "   from products;\n",
    "   //result,其他聚合函数同理\n",
    "   avg_price\n",
    "   16.133571\n",
    "   ```\n",
    "\n",
    "2. 聚合函数可理解为，计算一组的五个信息。没有进行分组，则代表将整体视作一组。所以只返回一组。\n",
    "\n",
    "3. count特殊之处\n",
    "\n",
    "   1. count(*) :对表中的行数进行计数（包括null行）\n",
    "   2. count(1) :对表中的行数进行计数（包括null行）；区别不是很大，与第一列的索引有关系\n",
    "   3. count(col_name) : 对col_name指定列进行计数（忽略null）\n",
    "   4. 所以12与3得到的结果会有差异\n",
    "\n",
    "4. 聚合函数中的参数，可以是计算字段，但不能是聚合函数（聚合函数不能嵌套）\n",
    "\n",
    "5. 组聚合； select后面可以跟多个聚合函数，只要你愿意。\n",
    "\n",
    "6. count(*)不可以使用distinct；count(col_name)可以\n",
    "\n",
    "   \n",
    "\n",
    "## 2.5 转换函数\n",
    "\n",
    "```\n",
    "cast //类型转换\n",
    "coalesce //null转其他值\n",
    "\n",
    "```\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2224a21",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
